#include <QDebug>
#include <QFileDialog>
#include <QSqlQuery>
#include <QSqlError>
#include <QMessageBox>
#include "exportexcel2003xmldialog.h"
#include "ui_exportexcel2003xmldialog.h"

namespace{
    bool operator <(const ExportExcel2003XMLDialog::Task& t1, const ExportExcel2003XMLDialog::Task& t2){
        if (t1.userID < t2.userID)
            return true;
        else if (t1.userID == t2.userID)
            if (t1.productID < t2.productID)
                return true;
            else if (t1.productID == t2.productID)
                if (t1.componentID < t2.componentID)
                    return true;
                else if (t1.componentID == t2.componentID)
                    if (t1.taskID < t2.taskID)
                        return true;
        return false;
    }

    typedef QMap<QDate, qreal> WorkingHours;
    typedef QMap<ExportExcel2003XMLDialog::Task, WorkingHours>  WorkingHoursPerTask;
}


ExportExcel2003XMLDialog::ExportExcel2003XMLDialog(QWidget *parent, QSqlDatabase db)
    : QDialog(parent)
    , ui(new Ui::ExportExcel2003XMLDialog)
    , db_(db)
{
    ui->setupUi(this);

    QDate today = QDate::currentDate();
    ui->startDateEdit->setDate(today.addMonths(-1).addDays(1));
    ui->endDateEdit->setDate(today);

    ui->buttonBox->button(QDialogButtonBox::Ok)->setEnabled(noError());
}

ExportExcel2003XMLDialog::~ExportExcel2003XMLDialog()
{
    delete ui;
}

void ExportExcel2003XMLDialog::accept()
{
    if (write(ui->filePathEdit->text()) == true)
        return QDialog::accept();
}

bool ExportExcel2003XMLDialog::noError() const
{
    return ui->startDateEdit->date() <= ui->endDateEdit->date() && ui->filePathEdit->text().isEmpty() == false;
}

void ExportExcel2003XMLDialog::collectData()
{
    taskList_.clear();

    QDateTime s = ui->startDateEdit->dateTime();
    QDateTime e = ui->endDateEdit->dateTime().addDays(1);

    QString q = QString(
                    "SELECT A.startTime, A.endTime, U.id, U.name, P.id, P.name, CO.id, CO.name, TC.id, TC.name, TC.assets, TC.billable, CY.id, CY.name, A.comment"
                    " from ACTUAL AS A, USERS AS U, PRODUCT AS P, TASKCATEGORY as TC, COMPANY AS CY, CLIENTUSERS AS CL"
                    " LEFT JOIN COMPONENT AS CO ON a.compid=co.id"
                    " where A.userid=U.id and A.prodid=P.id and A.cateid=TC.id and P.clientID=CL.id and CL.companyID=CY.id and"
                    " A.startTime>='%1' and A.endTime<='%2'"
                    " ORDER BY A.userID, A.prodID, A.compID, A.cateID, A.startTime").arg(s.toString(Qt::ISODate)).arg(e.toString(Qt::ISODate));
    QSqlQuery query = db_.exec(q);
    if (query.lastError().isValid()) {
        qDebug() << query.lastError().text();
        return;
    }

    while (query.next()) {
        Task task;
        task.startTime = query.value(0).toDateTime();
        task.endTime = query.value(1).toDateTime();
        task.userID = query.value(2).toInt();
        task.userName = query.value(3).toString();
        task.productID = query.value(4).toInt();
        task.productName = query.value(5).toString();
        task.componentID = query.value(6).toInt();
        task.componentName = query.value(7).toString();
        task.taskID = query.value(8).toInt();
        task.taskName = query.value(9).toString();
        task.assets = query.value(10).toBool();
        task.billable = query.value(11).toBool();
        task.clientID = query.value(12).toInt();
        task.clientName = query.value(13).toString();
        task.comment = query.value(14).toString();
        taskList_.push_back(task);
    }
}

bool ExportExcel2003XMLDialog::write(const QString& fname)
{
    QFile f(fname);
    if (f.open(QFile::WriteOnly) == false) {
        QMessageBox::critical(this, QString(), tr("File open failed: %1").arg(fname));
        return false;
    }

    f.write("<?xml version=\"1.0\" encoding=\"utf-8\"?>\n");
    f.write("<?mso-application progid=\"Excel.Sheet\"?>\n");

    f.write("<ss:Workbook xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\">\n");

    f.write("\t<ss:Styles>\n");

    f.write("\t\t<ss:Style ss:ID=\"headerStyle\">\n");
    f.write("\t\t\t<ss:Font ss:Bold=\"1\"/>\n");
    f.write("\t\t\t<ss:Borders>\n");
    f.write("\t\t\t<ss:Border ss:Position=\"Bottom\" ss:LineStyle=\"Double\" ss:Weight=\"1\" ss:Color=\"#000000\"/>\n");
    f.write("\t\t\t</ss:Borders>\n");
    f.write("\t\t</ss:Style>\n");

    f.write("\t\t<ss:Style ss:ID=\"headerDate\">\n");
    f.write("\t\t\t<ss:NumberFormat ss:Format=\"dd\"/>\n");
    f.write("\t\t\t<ss:Font ss:Bold=\"1\"/>\n");
    f.write("\t\t\t<ss:Borders>\n");
    f.write("\t\t\t\t<ss:Border ss:Position=\"Bottom\" ss:LineStyle=\"Double\" ss:Weight=\"1\" ss:Color=\"#000000\"/>\n");
    f.write("\t\t\t</ss:Borders>\n");
    f.write("\t\t</ss:Style>\n");

    f.write("\t\t<ss:Style ss:ID=\"bodyDateTime\">\n");
    f.write("\t\t\t<ss:NumberFormat ss:Format=\"yyyy/mm/dd hh:mm\"/>\n");
    f.write("\t\t</ss:Style>\n");

    f.write("\t\t<ss:Style ss:ID=\"bodyTimeSpan\">\n");
    f.write("\t\t\t<ss:NumberFormat ss:Format=\"0.0\"/>\n");
    f.write("\t\t</ss:Style>\n");

    f.write("\t</ss:Styles>\n");

    collectData();
    writeSummarySheet(f);
    writeUserDetailsSheet(f);

    f.write("</ss:Workbook>");

    return true;
}

void ExportExcel2003XMLDialog::writeSummarySheet(QFile& f)
{
    f.write("\t<ss:Worksheet ss:Name=\"All\">\n");

    f.write("\t\t<ss:Table>\n");
    f.write("\t\t\t<ss:Column ss:Width=\"50\"/>\n");
    f.write("\t\t\t<ss:Column ss:Width=\"80\"/>\n");
    f.write("\t\t\t<ss:Column ss:Width=\"50\"/>\n");
    f.write("\t\t\t<ss:Column ss:Width=\"220\"/>\n");
    f.write("\t\t\t<ss:Column ss:Width=\"50\"/>\n");
    f.write("\t\t\t<ss:Column ss:Width=\"80\"/>\n");
    f.write("\t\t\t<ss:Column ss:Width=\"50\"/>\n");
    f.write("\t\t\t<ss:Column ss:Width=\"80\"/>\n");
    f.write("\t\t\t<ss:Column ss:Width=\"50\"/>\n");
    f.write("\t\t\t<ss:Column ss:Width=\"80\"/>\n");
    f.write("\t\t\t<ss:Column ss:Width=\"50\"/>\n");

    QDate s = ui->startDateEdit->dateTime().date();
    QDate e = ui->endDateEdit->dateTime().date().addDays(1);
    QDate date = s;
    while (date < e) {
        f.write("\t\t\t<ss:Column ss:Width=\"40\"/>\n");
        date = date.addDays(1);
    }

    // add row for date
    f.write("\t\t\t<ss:Row ss:StyleID=\"headerStyle\">\n");
    f.write( QString("\t\t\t\t<ss:Cell><ss:Data ss:Type=\"String\">%1</ss:Data></ss:Cell>\n").arg(tr("User ID")).toUtf8() );
    f.write( QString("\t\t\t\t<ss:Cell><ss:Data ss:Type=\"String\">%1</ss:Data></ss:Cell>\n").arg(tr("User Name")).toUtf8() );
    f.write( QString("\t\t\t\t<ss:Cell><ss:Data ss:Type=\"String\">%1</ss:Data></ss:Cell>\n").arg(tr("Product ID")).toUtf8() );
    f.write( QString("\t\t\t\t<ss:Cell><ss:Data ss:Type=\"String\">%1</ss:Data></ss:Cell>\n").arg(tr("Product Name")).toUtf8() );
    f.write( QString("\t\t\t\t<ss:Cell><ss:Data ss:Type=\"String\">%1</ss:Data></ss:Cell>\n").arg(tr("Component ID")).toUtf8() );
    f.write( QString("\t\t\t\t<ss:Cell><ss:Data ss:Type=\"String\">%1</ss:Data></ss:Cell>\n").arg(tr("Component Name")).toUtf8() );
    f.write( QString("\t\t\t\t<ss:Cell><ss:Data ss:Type=\"String\">%1</ss:Data></ss:Cell>\n").arg(tr("Task ID")).toUtf8() );
    f.write( QString("\t\t\t\t<ss:Cell><ss:Data ss:Type=\"String\">%1</ss:Data></ss:Cell>\n").arg(tr("Task Name")).toUtf8() );
    f.write( QString("\t\t\t\t<ss:Cell><ss:Data ss:Type=\"String\">%1</ss:Data></ss:Cell>\n").arg(tr("Client ID")).toUtf8() );
    f.write( QString("\t\t\t\t<ss:Cell><ss:Data ss:Type=\"String\">%1</ss:Data></ss:Cell>\n").arg(tr("Client Name")).toUtf8() );
    f.write( QString("\t\t\t\t<ss:Cell><ss:Data ss:Type=\"String\">%1</ss:Data></ss:Cell>\n").arg(tr("Assets")).toUtf8() );
    f.write( QString("\t\t\t\t<ss:Cell><ss:Data ss:Type=\"String\">%1</ss:Data></ss:Cell>\n").arg(tr("Billable")).toUtf8() );

    date = s;
    while (date < e) {
        if (date == s)
            f.write("\t\t\t\t<ss:Cell ss:StyleID=\"headerDate\">\n");
        else
            f.write("\t\t\t\t<ss:Cell ss:Formula=\"=RC[-1]+1\" ss:StyleID=\"headerDate\">\n");
        f.write( QString("\t\t\t\t\t<ss:Data ss:Type=\"DateTime\">%1</ss:Data>\n").arg(date.toString("yyyy-MM-ddT00:00:00.000")).toUtf8() );
        f.write("\t\t\t\t</ss:Cell>\n");

        date = date.addDays(1);
    }

    f.write( QString("\t\t\t\t<ss:Cell><ss:Data ss:Type=\"String\">%1</ss:Data></ss:Cell>\n").arg(tr("Total")).toUtf8() );
    f.write("\t\t\t</ss:Row>\n");

    WorkingHoursPerTask hours;
    foreach (const Task& task, taskList_)
        hours[task][task.startTime.date()] += task.startTime.secsTo(task.endTime) / 60.0 / 60.0;

    WorkingHoursPerTask::const_iterator iter = hours.begin();
    while(iter != hours.end()) {
        f.write("\t\t\t<ss:Row>\n");

        f.write( QString("\t\t\t\t<ss:Cell><ss:Data ss:Type=\"Number\">%1</ss:Data></ss:Cell>\n").arg(iter.key().userID).toUtf8() );
        f.write( QString("\t\t\t\t<ss:Cell><ss:Data ss:Type=\"String\">%1</ss:Data></ss:Cell>\n").arg(iter.key().userName).toUtf8() );
        f.write( QString("\t\t\t\t<ss:Cell><ss:Data ss:Type=\"Number\">%1</ss:Data></ss:Cell>\n").arg(iter.key().productID).toUtf8() );
        f.write( QString("\t\t\t\t<ss:Cell><ss:Data ss:Type=\"String\">%1</ss:Data></ss:Cell>\n").arg(iter.key().productName).toUtf8() );
        f.write( QString("\t\t\t\t<ss:Cell><ss:Data ss:Type=\"Number\">%1</ss:Data></ss:Cell>\n").arg(iter.key().componentID).toUtf8() );
        f.write( QString("\t\t\t\t<ss:Cell><ss:Data ss:Type=\"String\">%1</ss:Data></ss:Cell>\n").arg(iter.key().componentName).toUtf8() );
        f.write( QString("\t\t\t\t<ss:Cell><ss:Data ss:Type=\"Number\">%1</ss:Data></ss:Cell>\n").arg(iter.key().taskID).toUtf8() );
        f.write( QString("\t\t\t\t<ss:Cell><ss:Data ss:Type=\"String\">%1</ss:Data></ss:Cell>\n").arg(iter.key().taskName).toUtf8() );
        f.write( QString("\t\t\t\t<ss:Cell><ss:Data ss:Type=\"Number\">%1</ss:Data></ss:Cell>\n").arg(iter.key().clientID).toUtf8() );
        f.write( QString("\t\t\t\t<ss:Cell><ss:Data ss:Type=\"String\">%1</ss:Data></ss:Cell>\n").arg(iter.key().clientName).toUtf8() );
        f.write( QString("\t\t\t\t<ss:Cell><ss:Data ss:Type=\"Number\">%1</ss:Data></ss:Cell>\n").arg(iter.key().assets).toUtf8() );
        f.write( QString("\t\t\t\t<ss:Cell><ss:Data ss:Type=\"Number\">%1</ss:Data></ss:Cell>\n").arg(iter.key().billable).toUtf8() );

        qreal total = 0;
        date = s;
        while (date < e) {

            WorkingHours::const_iterator iter2 = iter.value().find(date);
            if (iter2 != iter.value().end()) {
                f.write("\t\t\t\t<ss:Cell>\n");
                f.write( QString("\t\t\t\t\t<ss:Data ss:Type=\"Number\">%1</ss:Data>\n").arg(iter2.value()).toUtf8() );
                f.write("\t\t\t\t</ss:Cell>\n");
                total += iter2.value();
            }
            else
                f.write("\t\t\t\t<ss:Cell/>\n");

            date = date.addDays(1);
        }

        int days = s.daysTo(e);
        f.write( QString("\t\t\t\t<ss:Cell ss:Formula=\"=sum(RC[%1]:RC[-1])\">\n").arg(days*-1).toUtf8() );
        f.write( QString("\t\t\t\t\t<ss:Data ss:Type=\"Number\">%1</ss:Data>\n").arg(total).toUtf8() );
        f.write("\t\t\t\t</ss:Cell>\n");

        WorkingHours::const_iterator iter2 = iter.value().begin();
        while(iter2 != iter.value().end()) {
            ++iter2;
        }

        f.write("\t\t\t</ss:Row>\n");
        ++iter;
    }

    f.write("\t\t</ss:Table>\n");
    f.write("\t</ss:Worksheet>\n");
}

void ExportExcel2003XMLDialog::writeUserDetailsSheet(QFile& f)
{
    f.write( QString("\t<ss:Worksheet ss:Name=\"Details\">\n").toUtf8() );

    f.write("\t\t<ss:Table>\n");

    // add row for date
    f.write("\t\t\t<ss:Row ss:StyleID=\"headerStyle\">\n");
    f.write( QString("\t\t\t\t<ss:Cell><ss:Data ss:Type=\"String\">%1</ss:Data></ss:Cell>\n").arg(tr("User ID")).toUtf8() );
    f.write( QString("\t\t\t\t<ss:Cell><ss:Data ss:Type=\"String\">%1</ss:Data></ss:Cell>\n").arg(tr("User Name")).toUtf8() );
    f.write( QString("\t\t\t\t<ss:Cell><ss:Data ss:Type=\"String\">%1</ss:Data></ss:Cell>\n").arg(tr("Product ID")).toUtf8() );
    f.write( QString("\t\t\t\t<ss:Cell><ss:Data ss:Type=\"String\">%1</ss:Data></ss:Cell>\n").arg(tr("Product Name")).toUtf8() );
    f.write( QString("\t\t\t\t<ss:Cell><ss:Data ss:Type=\"String\">%1</ss:Data></ss:Cell>\n").arg(tr("Component ID")).toUtf8() );
    f.write( QString("\t\t\t\t<ss:Cell><ss:Data ss:Type=\"String\">%1</ss:Data></ss:Cell>\n").arg(tr("Component Name")).toUtf8() );
    f.write( QString("\t\t\t\t<ss:Cell><ss:Data ss:Type=\"String\">%1</ss:Data></ss:Cell>\n").arg(tr("Task ID")).toUtf8() );
    f.write( QString("\t\t\t\t<ss:Cell><ss:Data ss:Type=\"String\">%1</ss:Data></ss:Cell>\n").arg(tr("Task Name")).toUtf8() );
    f.write( QString("\t\t\t\t<ss:Cell><ss:Data ss:Type=\"String\">%1</ss:Data></ss:Cell>\n").arg(tr("Client ID")).toUtf8() );
    f.write( QString("\t\t\t\t<ss:Cell><ss:Data ss:Type=\"String\">%1</ss:Data></ss:Cell>\n").arg(tr("Client Name")).toUtf8() );
    f.write( QString("\t\t\t\t<ss:Cell><ss:Data ss:Type=\"String\">%1</ss:Data></ss:Cell>\n").arg(tr("Assets")).toUtf8() );
    f.write( QString("\t\t\t\t<ss:Cell><ss:Data ss:Type=\"String\">%1</ss:Data></ss:Cell>\n").arg(tr("Billable")).toUtf8() );
    f.write( QString("\t\t\t\t<ss:Cell><ss:Data ss:Type=\"String\">%1</ss:Data></ss:Cell>\n").arg(tr("Comment")).toUtf8() );
    f.write( QString("\t\t\t\t<ss:Cell><ss:Data ss:Type=\"String\">%1</ss:Data></ss:Cell>\n").arg(tr("Start Time")).toUtf8() );
    f.write( QString("\t\t\t\t<ss:Cell><ss:Data ss:Type=\"String\">%1</ss:Data></ss:Cell>\n").arg(tr("End Time")).toUtf8() );
    f.write( QString("\t\t\t\t<ss:Cell><ss:Data ss:Type=\"String\">%1</ss:Data></ss:Cell>\n").arg(tr("Working Time")).toUtf8() );
    f.write("\t\t\t</ss:Row>\n");

    foreach(const Task& task, taskList_) {
        f.write("\t\t\t<ss:Row>\n");

        f.write( QString("\t\t\t\t<ss:Cell><ss:Data ss:Type=\"Number\">%1</ss:Data></ss:Cell>\n").arg(task.userID).toUtf8() );
        f.write( QString("\t\t\t\t<ss:Cell><ss:Data ss:Type=\"String\">%1</ss:Data></ss:Cell>\n").arg(task.userName).toUtf8() );
        f.write( QString("\t\t\t\t<ss:Cell><ss:Data ss:Type=\"Number\">%1</ss:Data></ss:Cell>\n").arg(task.productID).toUtf8() );
        f.write( QString("\t\t\t\t<ss:Cell><ss:Data ss:Type=\"String\">%1</ss:Data></ss:Cell>\n").arg(task.productName).toUtf8() );
        f.write( QString("\t\t\t\t<ss:Cell><ss:Data ss:Type=\"Number\">%1</ss:Data></ss:Cell>\n").arg(task.componentID).toUtf8() );
        f.write( QString("\t\t\t\t<ss:Cell><ss:Data ss:Type=\"String\">%1</ss:Data></ss:Cell>\n").arg(task.componentName).toUtf8() );
        f.write( QString("\t\t\t\t<ss:Cell><ss:Data ss:Type=\"Number\">%1</ss:Data></ss:Cell>\n").arg(task.taskID).toUtf8() );
        f.write( QString("\t\t\t\t<ss:Cell><ss:Data ss:Type=\"String\">%1</ss:Data></ss:Cell>\n").arg(task.taskName).toUtf8() );
        f.write( QString("\t\t\t\t<ss:Cell><ss:Data ss:Type=\"Number\">%1</ss:Data></ss:Cell>\n").arg(task.clientID).toUtf8() );
        f.write( QString("\t\t\t\t<ss:Cell><ss:Data ss:Type=\"String\">%1</ss:Data></ss:Cell>\n").arg(task.clientName).toUtf8() );
        f.write( QString("\t\t\t\t<ss:Cell><ss:Data ss:Type=\"Number\">%1</ss:Data></ss:Cell>\n").arg(task.assets).toUtf8() );
        f.write( QString("\t\t\t\t<ss:Cell><ss:Data ss:Type=\"Number\">%1</ss:Data></ss:Cell>\n").arg(task.billable).toUtf8() );
        f.write( QString("\t\t\t\t<ss:Cell><ss:Data ss:Type=\"String\">%1</ss:Data></ss:Cell>\n").arg(task.comment).toUtf8() );
        f.write( QString("\t\t\t\t<ss:Cell ss:StyleID=\"bodyDateTime\"><ss:Data ss:Type=\"DateTime\">%1</ss:Data></ss:Cell>\n").arg(task.startTime.toString(Qt::ISODate)).toUtf8() );
        f.write( QString("\t\t\t\t<ss:Cell ss:StyleID=\"bodyDateTime\"><ss:Data ss:Type=\"DateTime\">%1</ss:Data></ss:Cell>\n").arg(task.endTime.toString(Qt::ISODate)).toUtf8() );
        f.write( QString("\t\t\t\t<ss:Cell ss:StyleID=\"bodyTimeSpan\" ss:Formula=\"=(RC[-1]-RC[-2])*24\"><ss:Data ss:Type=\"Number\"></ss:Data></ss:Cell>\n").toUtf8() );

        f.write("\t\t\t</ss:Row>\n");
    }

    f.write("\t\t</ss:Table>\n");

    f.write("\t</ss:Worksheet>\n");
}

void ExportExcel2003XMLDialog::on_startDateEdit_dateChanged(const QDate& date)
{
    ui->buttonBox->button(QDialogButtonBox::Ok)->setEnabled(noError());
}

void ExportExcel2003XMLDialog::on_endDateEdit_dateChanged(const QDate& date)
{
    ui->buttonBox->button(QDialogButtonBox::Ok)->setEnabled(noError());
}

void ExportExcel2003XMLDialog::on_filePathEdit_textChanged(const QString &arg1)
{
    ui->buttonBox->button(QDialogButtonBox::Ok)->setEnabled(noError());
}

void ExportExcel2003XMLDialog::on_browseButton_clicked()
{
    QString fname = QFileDialog::getSaveFileName(this, QString(), QString(), "Excel XML (*.xml);;All Files (*.*)");
    if (fname.isEmpty() == false)
        ui->filePathEdit->setText(fname);
}
